using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for PersonDAO
/// </summary>
public class PersonDAO : DBAccess
{

    #region sql Commands
    string SQL_SELECT_ALL = "select * from Person";
    string SQL_SELECT_BY_ID = "select * from Person where ID=@id";
    string SQL_INSERT = "insert into Person(Firstname,Lastname,DateOfBirth,Gender,Address,City, State, Country, Phone, Email, ModifiedDate,ModifiedBy) values(@Firstname,@Lastname,@DateOfBirth,@Gender,@Address,@City, @State, @Country, @Phone, @Email, @ModifiedDate,@ModifiedBy)";
    string SQL_UPDATE = "update Person set Firstname = @Firstname, Lastname = @Lastname, Address = @Address, City = @City, State = @State, Country = @Country, Phone = @Phone,Email= @Email, ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy where ID = @id";
    string SQL_DELETE = "delete from Person where ID = @id";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public PersonDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get person by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Person GetPerson(int id)
    {
        Person per = new Person();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    per.Id = dr.GetInt32(0);
                    per.FirstName = dr.GetString(1);
                    per.LastName = dr.GetString(2);
                    per.DateOfBirth = dr.GetDateTime(3);
                    per.Gender = dr.GetBoolean(4);
                    per.Address = dr.GetString(5);
                    per.City = dr.GetString(6);
                    per.State = dr.GetString(7);
                    per.Country = dr.GetString(8);
                    per.Phone = dr.GetString(9);
                    per.Email = dr.GetString(10);
                    per.ModifiedDate = dr.GetDateTime(11);
                    per.ModifiedBy = dr.GetInt32(12);
                    per.Note = dr.GetString(13);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return per;
    }
    /// <summary>
    /// Get all people 
    /// </summary>
    /// <returns></returns>
    public IList<Person> GetAllPersons()
    {
        IList<Person> list = new List<Person>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Person per = new Person();
                    per.Id = dr.GetInt32(0);
                    per.FirstName = dr.GetString(1);
                    per.LastName = dr.GetString(2);
                    per.Address = dr.GetString(3);
                    per.City = dr.GetString(4);
                    per.State = dr.GetString(5);
                    per.Country = dr.GetString(6);
                    per.Phone = dr.GetString(7);
                    per.Email = dr.GetString(8);
                    per.ModifiedDate = dr.GetDateTime(9);
                    per.ModifiedBy = dr.GetInt32(10);

                    list.Add(per);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Insert new person into database
    /// </summary>
    /// <param name="per"></param>
    /// <returns></returns>
    public int Insert(Person per)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_INSERT, conn);
            command.Parameters.AddWithValue("@Firstname", per.FirstName);
            command.Parameters.AddWithValue("@Lastname", per.LastName);
            command.Parameters.AddWithValue("@DateOfBirth", per.DateOfBirth);
            command.Parameters.AddWithValue("@Gender", per.Gender);
            command.Parameters.AddWithValue("@Address", per.Address);
            command.Parameters.AddWithValue("@City", per.City);
            command.Parameters.AddWithValue("@State", per.State);
            command.Parameters.AddWithValue("@Country", per.Country);
            command.Parameters.AddWithValue("@Phone", per.Phone);
            command.Parameters.AddWithValue("@Email", per.Email);
            command.Parameters.AddWithValue("@ModifiedDate", per.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", per.ModifiedBy);

            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                command.CommandText = "select @@IDENTITY from Person";
                per.Id = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Update Person
    /// </summary>
    /// <param name="per"></param>
    /// <returns></returns>
    public int Update(Person per)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
            command.Parameters.AddWithValue("@Firstname", per.FirstName);
            command.Parameters.AddWithValue("@Lastname", per.LastName);
            command.Parameters.AddWithValue("@Address", per.Address);
            command.Parameters.AddWithValue("@City", per.City);
            command.Parameters.AddWithValue("@State", per.State);
            command.Parameters.AddWithValue("@Country", per.Country);
            command.Parameters.AddWithValue("@Phone", per.Phone);
            command.Parameters.AddWithValue("@Email", per.Email);
            command.Parameters.AddWithValue("@ModifiedDate", per.ModifiedDate);
            command.Parameters.AddWithValue("@ModifiedBy", per.ModifiedBy);

            command.Parameters.AddWithValue("@id", per.Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Remove person out of database
    /// </summary>
    /// <param name="per"></param>
    /// <returns></returns>
    public int Delete(int  Id)
    {
        int effectedRows = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_DELETE, conn);
            command.Parameters.AddWithValue("@id", Id);
            if (Connect())
            {
                effectedRows = command.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRows;
    }

    /// <summary>
    /// Get list of persons with paging
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <returns></returns>
    public IList<Person> GetPersons(int currentPage, int pageSize, ref int total)
    {
        IList<Person> list = new List<Person>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPersons", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();

                while (dr.Read())
                {
                    Person person = new Person();

                    person.Id = dr.GetInt32(1);
                    person.FirstName = dr.GetString(2);
                    person.LastName = dr.GetString(3);
                    if (!dr.IsDBNull(4)) person.DateOfBirth = dr.GetDateTime(4);
                    if (!dr.IsDBNull(5)) person.Gender = dr.GetBoolean(5);
                    if (!dr.IsDBNull(6)) person.Address = dr.GetString(6);
                    if (!dr.IsDBNull(7)) person.City = dr.GetString(7);
                    if (!dr.IsDBNull(8)) person.State = dr.GetString(8);
                    if (!dr.IsDBNull(9)) person.Country = dr.GetString(9);
                    if (!dr.IsDBNull(10)) person.Phone = dr.GetString(10);
                    if (!dr.IsDBNull(11)) person.Email = dr.GetString(11);
                    person.ModifiedDate = dr.GetDateTime(12);
                    if (!dr.IsDBNull(13)) person.ModifiedBy = dr.GetInt32(13);
                    if (!dr.IsDBNull(14)) person.Note = dr.GetString(14);
                    list.Add(person);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    /// <summary>
    /// Select list of persons with paging and condition
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <param name="name"></param>
    /// <returns></returns>
    public IList<Person> GetPersonsExt(int currentPage, int pageSize, ref int total, string name)
    {
        IList<Person> list = new List<Person>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectPersonsExt", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@name", SqlDbType.NVarChar);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@name"].Value = name;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();

                while (dr.Read())
                {
                    Person person = new Person();

                    person.Id = dr.GetInt32(1);
                    person.FirstName = dr.GetString(2);
                    person.LastName = dr.GetString(3);
                    if (!dr.IsDBNull(4)) person.DateOfBirth = dr.GetDateTime(4);
                    if (!dr.IsDBNull(5)) person.Gender = dr.GetBoolean(5);
                    if (!dr.IsDBNull(6)) person.Address = dr.GetString(6);
                    if (!dr.IsDBNull(7)) person.City = dr.GetString(7);
                    if (!dr.IsDBNull(8)) person.State = dr.GetString(8);
                    if (!dr.IsDBNull(9)) person.Country = dr.GetString(9);
                    if (!dr.IsDBNull(10)) person.Phone = dr.GetString(10);
                    if (!dr.IsDBNull(11)) person.Email = dr.GetString(11);
                    person.ModifiedDate = dr.GetDateTime(12);
                    if (!dr.IsDBNull(13)) person.ModifiedBy = dr.GetInt32(13);
                    if (!dr.IsDBNull(14)) person.Note = dr.GetString(14);
                    list.Add(person);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    #endregion

}
